The World of Computer Software/The World of Computer Software.iso/opsprd.zip/OPSPREAD.DOC

Text File | 1992-04-13 | 23KB | 519 lines

                OPSPREAD - Spreadsheet-like PickLists
               Copyright (c) 1992 TurboPower Software
                            January 1992

------- Overview ----------------------------------------------------

A common request for Object Professional has been the ability to
display or input information in a format similar to that of a
spreadsheet. It has always been possible to do this using an
EntryScreen or a ScrollingEntryScreen, but for spreadsheets of any
significant size the speed penalty and memory overhead are high
because of the flexibility inherent to an entry screen. A better tool
for this task is the PickList object, which has always had hooks to
offer different "list orientations". It is fairly straightforward to
use these hooks to make a pick list that has scrolling properties like
those of a spreadsheet.

OPSPREAD is a unit that derives a new object SpreadList from the
PickList. With a few minor exceptions, it inherits all of the
capabilities of the PickList, including protected items, flexwriting,
multiple choice lists, mouse support, scroll bars, resizability,
stream support, and so on. Because it inherits so much capability from
PickList, SpreadList adds very little code to a program: the entire
unit has about 4000 bytes of code, and some of that will be stripped
by the smart linker.

The SpreadList does not have built-in capability for item data entry
and expression evaluation. Exactly what your spreadsheet does is
specific to your program, and we didn't attempt to guess what that is.
Hooks inherent to the PickList make it easy for you to get control
when needed to accept keyboard entry, validate cells, draw scrolling
headers, and so on. One of the demo programs provided here shows you
how to implement these features in a typical application.

Because SpreadList is based on PickList, the total number of cells is
limited to 65535, or 254 on a side for a square sheet. Also, unlike
commercial spreadsheets, the columns of a SpreadList must all have the
same width.

NOTE: OPSPREAD requires Object Professional version 1.13 of later to
compile and use it successfully.


------- Using the SpreadList ----------------------------------------

The SpreadList object provides only a few new methods, so there's not
much new to learn. Here's a simple example program that shows how to
use the object.

program ExSpread;
uses
  OpCrt, OpString, OpRoot, OpCmd, OpFrame, OpWindow, OpPick, OpSpread;
const
  SpreadSize = 50;  {SpreadList contains SpreadSize*SpreadSize cells}
  ItemWidth = 7;    {Each item displays ItemWidth characters}
var
  SL : SpreadList;

{$F+}
procedure SpreadItem(Item : Word; Mode : pkMode;
                     var IType : pkItemType; var IString : String;
                     PickPtr : PickListPtr);
var
  Row : Word;
  Col : Word;
begin
  with SpreadListPtr(PickPtr)^ do begin
    Row := GetItemRow(Item);
    Col := GetItemCol(Item);
    IString := Long2Str(Row)+','+Long2Str(Col);
  end;
end;
{$F-}

begin
  SL.InitCustom(5, 3, 32, 20,
                DefaultColorSet,
                DefWindowOptions or wBordered,
                ItemWidth, SpreadSize, SpreadSize,
                SpreadItem, SingleChoice);
  if InitStatus <> 0 then begin
    WriteLn('Error initializing SpreadList');
    Halt;
  end;

  {Process it, then finish up}
  SL.Process;
  SL.Erase;
  SL.Done;
end.

Focus first on the call to SpreadList's constructor, InitCustom. Most
of the parameters are identical to what you would pass to a PickList:
window coordinates, colors, window options, and so on. The main
difference from calling a PickList constructor is that instead of
passing the total number of items in the list, you pass the number of
items on each axis of the spreadsheet. In this example, we're using a
square spreadsheet, and we pass SpreadSize (50) for the number of rows
and columns. The other difference is that there's no need to pass an
orientation (e.g., PickVertical) to the SpreadList, since it has a
built-in orientation that gives it scrolling properties like those of
a spreadsheet.

Now take a look at the item string procedure, named SpreadItem in this
example. This routine is declared exactly like one used with a
PickList. There are two differences in what it must do internally:
First, it must typecast the PickPtr parameter to type SpreadListPtr so
that it can refer to unique methods of the SpreadList. Second, it
usually must decompose the Item parameter passed to it into the
component Row and Col numbers of the spreadsheet. The SpreadList
object provides two methods for this purpose: GetItemRow and
GetItemCol. In this example, each item string is assigned the Row,Col
coordinate of the SpreadList cell.

The example program goes on to process, then erase and dispose of the
SpreadList.

If you run the example, you'll see that the spreadsheet cells are
labeled in just the order you would expect, and that scrolling works
just like a spreadsheet. If you are very familiar with PickList
behavior, you'll notice a couple of minor differences in the cursor
keypad handling. In the PickList, pressing <Home> and <End> moves the
highlight bar to the first and last element in the list, respectively.
In the SpreadList, <Home> and <End> move to the first and last cells
on the current row. <CtrlPgUp> and <CtrlPgDn> move to the top and
bottom cells of the current column, whereas these keystrokes are
ignored by the PickList.

More thorough examples are provided in the OPSPREAD archive.
TSPREAD.PAS demonstrates mouse and dragging support, vertical divider
bars, scroll bars, and the use of the GetItemNum method to position
the highlight to a particular cell before calling Process.

TSPREAD2.PAS derives a new object, SpreadSheet, from SpreadList.
SpreadSheet implements all the features needed for a simple
spreadsheet application. It shows how to associate a data structure
with each cell of the sheet, and how to edit user input for each cell.
It also demonstrates a completely object-oriented approach to using
OPSPREAD: No procedure pointers are used; instead the SpreadSheet
object overrides virtual methods where appropriate. The data for each
cell is stored in a two-dimensional array of strings, which is also
contained within the SpreadSheet object. The item string method simply
returns the string at the corresponding position within this array. Of
course, the spreadsheet data structure could be more complex, but the
item string procedure would still build a string to display from the
cell data for the specified item position.

Input editing is handled using two of the capabilities inherent to the
PickList. First, TSPREAD2 assigns the <Backspace> key as a user exit
command, so that PickList.ProcessSelf will exit whenever this key is
pressed. When this exit command is detected, TSPREAD2 deletes the last
character in the string associated with the current cell. TSPREAD2
also overrides PickList's ItemSearch method, in such a way that
PickList.ProcessSelf will exit whenever any non-extended key, with an
ASCII code 32 or greater, is pressed. TSPREAD2 detects this by
checking for the ccChar exit command; when it sees this exit command,
it validates the character (only numbers are accepted here) and
appends it to the string associated with the current cell. If the
character is not a number or the string is full, TSPREAD2 beeps. The
SpreadSheet object overrides the ProcessSelf method to hide these
editing details from any client program that uses it.

Many commercial spreadsheets allow simple editing of cell contents
using keystrokes such as those just described. When another function
key is pressed (<F2> in Lotus 1-2-3), however, a more advanced line
editor is used. The same functionality could be added to SpreadSheet
by mapping <F2> to a user exit command, and instantiating an OPRO
LineEditor to edit the contents of the current cell before returning
to SpreadSheet.Process.

TSPREAD2 overrides the PreMove method of PickList in order to draw
scrolling row and column headers on the spreadsheet. Since PickList
calls this method whenever it is ready to get the next command from
the keyboard or mouse, it is a convenient hook for drawing these
headers. SpreadSheet.PreMove checks a couple of variables within the
SpreadSheet object to minimize how often it redraws the headers. It
also calls the TopLeftRowCol method of SpreadList to determine the
spreadsheet row and column currently displayed in the top left corner
of the sheet. TSPREAD2 also overrides the UpdateContents method of
PickList in order to update the scrolling headers when the window is
resized using the mouse.

TSPREAD2 overrides two additional virtual methods that were added to
the PickList object just to make writing spreadsheets easier. Function
OKToChangeChoice is called whenever the PickList ProcessSelf method is
ready to move the highlight bar off of one item and on to another.
This hook is ideal for validating the contents of a spreadsheet cell.
TSPREAD2's implementation of this routine assures that the cell is not
empty; if it is, TSPREAD2 writes a warning message, waits for a key to
be pressed, then forces PickList to leave the cursor on the current
cell. Note that OKToChangeChoice should first confirm that the current
choice will really change as a result of the last entered command.
PickList doesn't spend the time to weed out events such as the user
clicking the mouse over the current item, or pressing <Home> when the
cursor is already in column 1. The EvaluateCmd method of PickList is
ideal for this purpose, as shown in SpreadSheet.OKToChangeChoice.

TSPREAD2 also overrides the PositionCursor method of PickList. By
default, PickList.PositionCursor positions the hardware cursor on the
first character of each pick item. This isn't usually important
because the cursor is hidden anyway. The SpreadSheet Init constructor
makes the hardware cursor visible to make input editing more
intuitive, so it is important to put the cursor in a sensible
location. SpreadSheet.PositionCursor shows how.

Although the SpreadSheet object demonstrated by TSPREAD2 doesn't do
everything you might need, it shows the fundamental techniques used to
add a spreadsheet to your application.


------- OPSPREAD Reference Section ----------------------------------

Constants
---------
  pkSpread          = 4;

This constant continues the series begun with pkNoOrient, pkVertical,
pkHorizontal, and pkSnaking in OPPICK.PAS. It indicates the
"orientation" of the PickList and will be returned by the
GetOrientation method when called for a SpreadList. You generally
won't need to use the pkSpread constant.

  otSpreadList      = 998;   {object type}
  veSpreadList      = 0;     {version code}
  ptPickSpread      = 998;   {pointer code for the pkSpread
                              orientation}

These constants are used by the Object Professional stream manager
when a SpreadList instance is stored in a stream. You generally won't
need to refer to them.

Types
-----
  SpreadListPtr = ^SpreadList;
  SpreadList =
    object(PickList)
      slRows : Word;
      slCols : Word;
      ...
    end;

The object used to implement spreadsheet-like behavior. slRows and
slCols store the number of "cells" along each axis. You shouldn't
modify these fields.

SpreadList Methods
------------------
SpreadList overrides two methods of PickList in order to disable
them. These methods are:

  procedure ChangeNumItems(NumItems : Word);
    {-Change the number of items to display}
  procedure ChangeOrientation(Orientation : pkGenlProc);
    {-Change the orientation}

If you call these methods, a runtime error 211 will result. Once a
SpreadList is instantiated, you cannot change the number of items in
it, or its orientation.

With these two exceptions, SpreadList inherits all of the methods of
the PickList, CommandWindow, StackWindow, RawWindow, and Frame
objects. Refer to the documentation for those objects for further
information.

The following sections document methods that are new to SpreadList.

Declaration
  function GetItemCol(Item : Word) : Word;
Purpose
  Return the column position of the specified item.
Description
  Given an Item number in the range 1 to slRows*slCols, GetItemCol
  returns the column number in the range 1 to slCols.
See Also
  GetItemRow

Declaration
  function GetItemNum(Row, Col : Word) : Word;
Purpose
  Return the item number corresponding to Row and Col.
Description
  Given valid Row and Col numbers, GetItemNum returns the linear item
  number. The value equals (Row-1)*slCols+Col. From this you can see
  that items are numbered in the following fashion (for slRows=3,
  slCols=5):
                  Col
             1  2  3  4  5
          +---------------
    Row 1 |  1  2  3  4  5
    Row 2 |  6  7  8  9 10
    Row 3 | 11 12 13 14 15

Declaration
  function GetItemRow(Item : Word) : Word;
Purpose
  Return the row position of the specified item.
Description
  Given an Item number in the range 1 to slRows*slCols, GetItemRow
  returns the row number in the range 1 to slRows.
See Also
  GetItemCol

Declarations
  constructor Init(X1, Y1, X2, Y2 : Byte;
                   ItemWidth : Byte;
                   NumRows : Word;
                   NumCols : Word;
                   StringProc : pkStringProc;
                   CommandHandler : pkGenlProc);
  constructor InitCustom(X1, Y1, X2, Y2 : Byte;
                         var Colors : ColorSet;
                         Options : LongInt;
                         ItemWidth : Byte;
                         NumRows : Word;
                         NumCols : Word;
                         StringProc : pkStringProc;
                         CommandHandler : pkGenlProc);
  constructor InitAbstract(X1, Y1, X2, Y2 : Byte;
                           var Colors : ColorSet;
                           Options : LongInt;
                           ItemWidth : Byte;
                           NumRows : Word;
                           NumCols : Word;
                           CommandHandler : pkGenlProc);
  constructor InitDeluxe(X1, Y1, X2, Y2 : Byte;
                         var Colors : ColorSet;
                         Options : LongInt;
                         ItemWidth : Byte;
                         NumRows : Word;
                         NumCols : Word;
                         StringProc : pkStringProc;
                         CommandHandler : pkGenlProc;
                         PickOptions : Word);
  constructor InitAbstractDeluxe(X1, Y1, X2, Y2 : Byte;
                                 var Colors : ColorSet;
                                 Options : LongInt;
                                 ItemWidth : Byte;
                                 NumRows : Word;
                                 NumCols : Word;
                                 CommandHandler : pkGenlProc;
                                 PickOptions : Word);
Purpose
  Initialize a SpreadList.
Description
  These constructors initialize a SpreadList with various degrees of
  control over options and colors.

  X1, Y1, X2, and Y2 specify the screen coordinates of the active
  portion of the window. Colors specifies the video attributes to use.
  SpreadList uses the attributes in exactly the same way as a PickList
  does. Options is a bit mask specifying the window options (see page
  4-60 of volume 1).

  ItemWidth is the number of screen columns used for each item column.
  For the best appearance, the width of the window (X2-X1+1) should be
  an exact multiple of ItemWidth, although the SpreadList will
  function correctly even if it is not. Remember that if vertical
  divider bars are enabled, the last character position in ItemWidth
  is reserved for the divider.

  NumRows and NumCols are the number of rows and columns in the
  spreadsheet. SpreadList will scroll horizontally and vertically to
  display any cells that don't fit within the window. The product
  NumRows*NumCols must be less than 65536 or the constructor will fail
  with error epFatal+ecBadParam.

  StringProc is a procedure whose parameters match those of type
  pkStringProc. It must be compiled FAR and not nested within any
  other procedures. Given the item number passed to it as a parameter,
  the StringProc must return a string to display for that item. In the
  context of a spreadsheet, the StringProc will generally first
  compute the Row and Col numbers that correspond to the item number
  by calling GetItemRow and GetItemCol. See the introductory example
  procedure SpreadItem in this file. The StringProc may also take any
  of the actions described for a PickList's StringProc, including
  marking items as protected or semiprotected. All of the constructors
  with Abstract in their name require that a descendant of SpreadList
  override the ItemString method of PickList instead of passing the
  StringProc parameter to the constructor.

  CommandHandler takes on one of the values SingleChoice or
  MultipleChoice. This determines whether the PickList allocates a
  BitSet to keep track of selected cells and accepts additional
  commands to select and deselect items. For a SpreadList, the value
  of CommandHandler will generally be SingleChoice.

  PickOptions is a bit mask that specifies options specific to a
  PickList. This mask is interpreted the same for a SpreadList
  as it is for a PickList, except that the pkAlterPageRow and
  pkNoHighlightPad options are ignored for a SpreadList.
See Also
  Load

Declaration
  {$IFDEF UseStreams}
  constructor Load(var S : IdStream);
Purpose
  Load a spread list from a stream.
Description
  SpreadList.Load works exactly like PickList.Load, except that it
  initializes the additional slRows and slCols fields of the object.

  Use procedure SpreadListStream to register types needed for reading
  SpreadList objects from a stream. Note that SpreadListStream
  registers the PickList orientation automatically, so there is no
  need for you to do so. You must still register the SingleChoice or
  MultipleChoice command handler, the StringProc (when you haven't
  overridden ItemString), and any character search or premove routines
  you are using.
See Also
  Store

Declaration
  function PickList.OKToChangeChoice : Boolean; virtual;
Purpose
  Called just prior to changing to a new choice in PickList.Process.
Description
  The default version of this method is implemented in the PickList
  object, not SpreadList. It is documented here because it is most
  likely to be overridden by users of SpreadList.

  The default OKToChangeChoice always returns True, meaning that the
  highlight bar can be moved off of the current item regardless of its
  contents. In a spreadsheet application, where the contents of each
  item can be changed by the user, it may be necessary to validate the
  contents of the current cell before allowing the user to leave it.
  If so, you should override OKToChangeChoice. The method should
  return True if the current cell (identified by calling
  GetLastChoice) is valid, or False if it is invalid. When
  OKToChangeChoice returns False, PickList leaves the highlight bar on
  the current item and ignores whatever command was last entered.

  If you wish to tell the user that the current cell is invalid,
  OKToChangeChoice should do so by beeping or displaying a warning
  line or dialog window. If it displays a warning it must restore the
  screen before returning.

  PickList.Process may call OKToChangeChoice in cases where the
  highlight bar won't be leaving the current item. For example, if the
  user clicks the mouse on a scroll bar, and the mouse position is
  such that the new item will be the same as the old item, Process
  will still call OKToChangeChoice. Determining that the current item
  remains unchanged takes additional CPU time that PickList.Process
  needn't spend for most PickList applications. As a result,
  OKToChangeChoice should check that the item will truly change before
  it validates the current cell. The EvaluateCmd method of PickList is
  ideal for doing this, since it simulates what would occur if a
  specified command were executed.

  Note that PickList.Process does *not* call OKToChangeChoice when an
  exit command is received via the keyboard or mouse. Therefore it is
  your application's responsibility to validate the current cell when
  Process exits. PickList doesn't do so because some exit commands may
  allow the user to edit the cell further (as the <BackSpace> key does
  in TPSPREAD2.PAS), or may be intended to perform an unrelated
  function and then immediately return to the spreadsheet's Process
  method.

  There is no procedure pointer that corresponds to this virtual
  method.

  See the OKToChangeChoice method in TSPREAD2.PAS for an example.

Declaration
  procedure PickList.PositionCursor(Item : Word;
                                    ACol, ARow : Byte); virtual;
Purpose
  Position the hardware cursor for selected item.
Description
  The default version of this method is implemented in the PickList
  object, not SpreadList. It is documented here because it is most
  likely to be overridden by users of SpreadList.

  Item is the number of the item that is currently selected. ACol and
  ARow are the absolute screen position of the first character of the
  item, including any leading pad characters. The default
  PositionCursor simply places the hardware cursor at ARow,ACol by
  calling GoToXYAbs(ACol, ARow). This is more than adequate for normal
  PickList applications since the hardware cursor is usually hidden
  anyway.

  For a SpreadList application, it may be desirable to position the
  hardware cursor at an alternate position, for example at the end of
  the string representing the contents of the cell. To do so, override
  PositionCursor and position the cursor as needed.

  There is no procedure pointer that corresponds to this virtual
  method.

  See the PositionCursor method in TSPREAD2.PAS for an example.

Declaration
  {$IFDEF UseStreams}
  procedure Store(var S : IdStream);
Purpose
  Store a spread list in a stream.
Description
  SpreadList.Store works exactly like PickList.Store, except that it
  stores the additional slRows and slCols fields of the object.

  See Load for additional information on pointer registration
  requirements of SpreadList.Store.
See Also
  Load

Declaration
  procedure TopLeftRowCol(var Row, Col : Word);
Purpose
  Return the Row and Col of the top left item.
Description
  This routine is primarily useful within a PreMove routine that
  draws scrolling row and column headers. See TSPREAD2.PAS for an
  example.


------- OPSPREAD Version History ------------------------------------
Version 1.13   1/15/92
  Initial release (synchronized with Object Professional 1.13)